/**
 * The database package of the application.
 */
package fr.umlv.m2.android.quizz.database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;

/**
 * This class defines the database level object.
 * 
 * @author Gambouele Bertrand.
 * @author Viravout Alexandre.
 */
public class LevelDb {
	/**
	 * The database version.
	 */
	private static final int DB_VERSION = 1;
	/**
	 * The database name.
	 */
	private static final String DB_NAME = "quizz.db";

	/**
	 * The level table name.
	 */
	private static final String LEVEL_TABLE = "level";
	/**
	 * The level id column name.
	 */
	private static final String ID = "id";
	/**
	 * The level id column number.
	 */
	private static final int ID_COL_NUM = 0;
	/**
	 * The level name column name.
	 */
	private static final String NAME = "name";
	/**
	 * The level name column number.
	 */
	private static final int NAME_COL_NUM = 1;
	/**
	 * The level number of question column name.
	 */
	private static final String NB_QUESTION = "nbQuestion";
	/**
	 * The level number of question column number.
	 */
	private static final int NB_QUESTION_COL_NUM = 2;

	/**
	 * The database of the application.
	 */
	private SQLiteDatabase db;
	/**
	 * The database open helper.
	 */
	private final QuizzDatabaseOpenHelper quizzSQLite;
	
	/**
	 * Create the database level object.
	 *  
	 * @param context the context.
	 */
	public LevelDb(Context context) {
		this.quizzSQLite = new QuizzDatabaseOpenHelper(context, DB_NAME, null, DB_VERSION);
	}

	/**
	 * Opens the database.
	 */
	public void open() {
		this.db = this.quizzSQLite.getWritableDatabase();
	}

	/**
	 * Closes the database.
	 */
	public void close() {
		this.db.close();
	}

	/**
	 * Gets the database.
	 * 
	 * @return the database.
	 */
	public SQLiteDatabase getDb() {
		return this.db;
	}
	
	/**
	 * Gets the number of level.
	 * 
	 * @return the number of level.
	 */
	public long getNbLevel() {
		return DatabaseUtils.queryNumEntries(this.db, LEVEL_TABLE);
	}
	
	/**
	 * Clear the level table.
	 */
	public void clear() {
		this.quizzSQLite.clearTable(this.db, LEVEL_TABLE);
	}

	/**
	 * Inserts a level in the database.
	 * 
	 * @param level the level to insert.
	 * @return the row ID of the newly inserted row, or -1 if an error occurred.
	 */
	public long insertLevel(Level level) {
		ContentValues values = new ContentValues();

		values.put(NAME, level.getName());
		values.put(NB_QUESTION, level.getNbQuestion());

		return this.db.insert(LEVEL_TABLE, null, values);
	}
	
	private Level cursorToLevel(Cursor cursor) {
		if(cursor.getCount() == 0) {
			return null;
		} else {
			cursor.moveToFirst();
		}
		
		Level level = new Level();
		level.setId(cursor.getInt(ID_COL_NUM));
		level.setName(cursor.getString(NAME_COL_NUM));
		level.setNbQuestion(cursor.getInt(NB_QUESTION_COL_NUM));
		cursor.close();
		
		return level;
	}
	
	/**
	 * Gets the level with his id.
	 * 
	 * @param id the level id.
	 * @return the level object, null if no row.
	 */
	public Level getLevelWithId(long id) {
		Cursor cursor = this.db.query(
				LEVEL_TABLE,
				new String[] { ID, NAME, NB_QUESTION },
				ID + " = " + id,
				null, null, null, null
		);
		
		return cursorToLevel(cursor); 
	}
	
	/**
	 * Gets the level with his name.
	 * 
	 * @param name the level name.
	 * @return the level object, null if no row.
	 */
	public Level getLevelWithName(String name) {
		Cursor cursor = this.db.query(
				LEVEL_TABLE,
				new String[] { ID, NAME, NB_QUESTION },
				NAME + " LIKE \"" + name + "\"",
				null, null, null, null
		);
		
		return cursorToLevel(cursor); 
	}

	private List<Level> cursorToLevels(Cursor cursor) {
		List<Level> levels = new ArrayList<Level>();
		
		if(cursor.getCount() == 0) {
			return null;
		} else {
			cursor.moveToFirst();
		}
		
		Level level;
		do {
			level = new Level();
			level.setId(cursor.getInt(ID_COL_NUM));
			level.setName(cursor.getString(NAME_COL_NUM));
			level.setNbQuestion(cursor.getInt(NB_QUESTION_COL_NUM));
			levels.add(level);
		} while (cursor.moveToNext());
		cursor.close();

		return levels;
	}
	
	/**
	 * Gets all levels of the database.
	 * 
	 * @return the list of level.
	 */
	public List<Level> getAllLevels() {
		Cursor cursor = this.db.query(
				LEVEL_TABLE,
				new String[] { ID, NAME, NB_QUESTION },
				null, null, null, null, null
		);

		return this.cursorToLevels(cursor);
	}
}
